<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjijxs extends Common
{
    public function getjxs($jid){
        $arr=db("cg_jxs")->where(["id"=>$jid])->find();
        return json_encode($arr);
    }
    //决策依据
    public function jcyj(){
        $fenqu=db::table("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
        $this->assign("fenqu",$fenqu);
        return $this->fetch();
    }
    //决策依据详情
    public function jxsyj($jid){

        $jxs=Db::table("cg_jxs")->where(["id"=>$jid])->find();
        $this->assign("jxs",$jxs);
        //当年
        $now=strtotime(date("Y-m-d"));
        $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        //当年考核期
        $kao2=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")
        ->field("k1.id as k1id,k1.title as k1title,k1.starttime as k1start,k1.endtime as k1end,k2.id as k2id,k2.title as k2title,k2.starttime as k2start,k2.endtime as k2end")->select();
        $zong["tr"]=0;$zong["fy"]=0;$zong["ys"]=0;$zong["tour"]=0;$zong["mbfylh"]=0;$zong["mbfylx"]=0;$zong["sjfylh"]=0;$zong["sjfylx"]=0;
        $ys=0;
        for($i=0;$i<count($kao2);$i++){
            $ys++;
            //计算天数
            $k1ts=($kao2[$i]["k1end"]-$kao2[$i]["k1start"])/3600/24+1;
            $k2ts=($kao2[$i]["k2end"]-$kao2[$i]["k2start"])/3600/24+1;
            //当年目标
            $kao2[$i]["tar"]=0;
            $sumk2tar=Db::table("target_apply")->alias("t")->join("target_apply_detail td","td.apply_id=t.id")
            ->where("t.type_id=5 and t.state=7 and t.tag<>1 and t.is_actived=1 and t.is_deleted=0 and td.jingxiao={$jxs["id"]} and td.erjikaoheqi={$kao2[$i]["k2id"]}")->sum("feiyong");
            if($sumk2tar)
                $kao2[$i]["tar"]+=$sumk2tar;
            $sumk1tar=Db::table("target_apply")->alias("t")->join("target_apply_detail td","td.apply_id=t.id")
            ->where("t.type_id=5 and t.state=7 and t.tag<>1 and t.is_actived=1 and t.is_deleted=0 and td.jingxiao={$jxs["id"]} and td.yijikaoheqi={$kao2[$i]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
            //echo Db::table("target_apply")->getLastsql();
            if($sumk1tar){
                $kao2[$i]["tar"]+=$sumk1tar/$k1ts*$k2ts;
            }
            $zong["tr"]+=$kao2[$i]["tar"];
            //发运
            $datast=date("Y-m-d",$kao2[$i]["k2start"]);
            $dataed=date("Y-m-d",$kao2[$i]["k2end"]);
            $sumfy=Db::Table("bx_fy")->where("sdAgency_id={$jxs["id"]} and fyDate>='$datast' and fyDate<='$dataed'")->sum("fyZheMoney");
            //echo Db::table("bx_fy")->getLastsql();
            if($sumfy){
                $kao2[$i]["fy"]=$sumfy;
            }else{
                $kao2[$i]["fy"]=0;
            }
            $zong["fy"]+=$kao2[$i]["fy"];
            //预算费用
            $kao2[$i]["ys"]=0;
            $sumk2ys=Db::Table("yusuan")->alias("y")->where("y.type=1 and y.jingxiao={$jxs["id"]} and erjikaoheqi={$kao2[$i]["k2id"]}")->sum("jine");
            if($sumk2ys){
                $kao2[$i]["ys"]+=$sumk2ys;
            }
            $sumk1ys=Db::Table("yusuan")->alias("y")->where("y.type=1 and y.jingxiao={$jxs["id"]} and yijikaoheqi={$kao2[$i]["k1id"]} and erjikaoheqi is null")->sum("jine");
            if($sumk1ys){
                $kao2[$i]["ys"]+=$sumk1ys/$k1ts*$k2ts;
            }
            $zong["ys"]+=$kao2[$i]["ys"];
            //投入额
            $kao2[$i]["tour"]=0;
            $sumks=Db::table("cg_ti")->alias("t")
            ->join("cg_ti_list td","t.id=td.ti_id")
            ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$kao2[$i]["k2start"]} and td.start<={$kao2[$i]["k2end"]} and t.jxs_id={$jxs["id"]}")->field("pizhunhanshui,totalExpectedSales,fb_shijitourufeiyong,fb_shijixiaoliang")->select();
            $kao2[$i]["mbfylh"]=0;$kao2[$i]["mbfylx"]=0;$kao2[$i]["sjfylh"]=0;$kao2[$i]["sjfylx"]=0;
            for($j=0;$j<count($sumks);$j++){
                if($sumks[$j]["fb_shijitourufeiyong"]){
                    $kao2[$i]["tour"]+=$sumks[$j]["fb_shijitourufeiyong"];
                    $kao2[$i]["sjfylh"]+=$sumks[$j]["fb_shijitourufeiyong"];
                    $kao2[$i]["sjfylx"]+=$sumks[$j]["fb_shijixiaoliang"];
                }else{
                    $kao2[$i]["tour"]+=$sumks[$j]["pizhunhanshui"];
                }
                $kao2[$i]["mbfylh"]+=$sumks[$j]["pizhunhanshui"];
                $kao2[$i]["mbfylx"]+=$sumks[$j]["totalExpectedSales"];
            }
            if($kao2[$i]["mbfylx"])
                $kao2[$i]["mbfyl"]=$kao2[$i]["mbfylh"]/$kao2[$i]["mbfylx"]*100;
            else
                $kao2[$i]["mbfyl"]=0;
            if($kao2[$i]["sjfylx"])
                $kao2[$i]["sjfyl"]=$kao2[$i]["sjfylh"]/$kao2[$i]["sjfylx"]*100;
            else
                $kao2[$i]["sjfyl"]=0;
            $zong["tour"]+=$kao2[$i]["tour"];
            $zong["mbfylh"]+=$kao2[$i]["mbfylh"];
            $zong["mbfylx"]+=$kao2[$i]["mbfylx"];
            $zong["sjfylh"]+=$kao2[$i]["sjfylh"];
            $zong["sjfylx"]+=$kao2[$i]["sjfylx"];
        }
        $zong["avgtr"]=$zong["tr"]/$ys;
        $zong["avgfy"]=$zong["fy"]/$ys;
        $zong["avgys"]=$zong["ys"]/$ys;
        $zong["avgtour"]=$zong["tour"]/$ys;
        if($zong["mbfylx"])
            $zong["mbfyl"]=$zong["mbfylh"]/$zong["mbfylx"]*100;
        else
            $zong["mbfyl"]=0;
        if($zong["sjfylx"])
            $zong["sjfyl"]=$zong["sjfylh"]/$zong["sjfylx"]*100;
        else
            $zong["sjfyl"]=0;
        $this->assign("zong",$zong);
        $this->assign("kao2",$kao2);
        //决策依据2
        $startday=strtotime("2017-01-01");$t=time();
        $cainian=Db::table("cg_cainian")->where("starttime>=$startday and starttime<=$t")->select();
        $zongcai=count($cainian);
        $hasc=[];
        for($i=0;$i<count($cainian);$i++){
            $sum=Db::table("cg_ti")->alias("t")
            ->join("cg_ti_list td","t.id=td.ti_id")
            ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$cainian[$i]["starttime"]} and td.start<={$cainian[$i]["endtime"]} and t.jxs_id={$jxs["id"]}")->sum("pizhunhanshui");
            
            if($sum)
                $hasc[]=$cainian[$i];
        }
        for($i=0;$i<count($hasc);$i++){
            $hasc[$i]["kaohe2"]=Db::table("cg_cainian")->alias("c")->join("cg_kaoheqi1 k1","k1.cid=c.id")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["c.id"=>$hasc[$i]["id"]])
            ->field("k2.id,k2.title,k2.starttime,k2.endtime")->order("k2.starttime")->select();
            //查询单据类型
            $hasc[$i]["feetype"]=Db::table("cg_ti")->alias("t")
            ->join("cg_ti_list td","t.id=td.ti_id")
            ->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
            ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$hasc[$i]["starttime"]} and td.start<={$hasc[$i]["endtime"]} and t.jxs_id={$jxs["id"]}")
            ->group("td.feetype")
            ->field("k3.title as feetype,k3.id")->select();
            //echo db("cg_ti")->getLastsql();
            $hasc[$i]["feec"]=count($hasc[$i]["feetype"]);
            $hasc[$i]["xiaojizong"]=0;
            for($k=0;$k<count($hasc[$i]["kaohe2"]);$k++){
                $hasc[$i]["xiaoji"][$k]=0;
                $tmp=Db::table("cg_ti")->alias("t")
                    ->join("cg_ti_list td","t.id=td.ti_id")->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$hasc[$i]["kaohe2"][$k]["starttime"]} and td.start<={$hasc[$i]["kaohe2"][$k]["endtime"]} and t.jxs_id={$jxs["id"]}")
                    ->field("td.pizhunhanshui,td.fb_shijitourufeiyong")->select();
                for($l=0;$l<count($tmp);$l++){
                    if($tmp[$l]["fb_shijitourufeiyong"])
                        $hasc[$i]["xiaoji"][$k]+=$tmp[$l]["fb_shijitourufeiyong"];
                    else
                        $hasc[$i]["xiaoji"][$k]+=$tmp[$l]["pizhunhanshui"];

                }
                $hasc[$i]["xiaojizong"]+=$hasc[$i]["xiaoji"][$k];
            }
            for($j=0;$j<count($hasc[$i]["feetype"]);$j++){
                $hasc[$i]["feetype"][$j]["zong"]=0;
                for($k=0;$k<count($hasc[$i]["kaohe2"]);$k++){
                    $tmp=Db::table("cg_ti")->alias("t")
                    ->join("cg_ti_list td","t.id=td.ti_id")->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$hasc[$i]["kaohe2"][$k]["starttime"]} and td.start<={$hasc[$i]["kaohe2"][$k]["endtime"]} and t.jxs_id={$jxs["id"]} and td.feetype={$hasc[$i]["feetype"][$j]["id"]}")
                    ->field("td.pizhunhanshui,td.fb_shijitourufeiyong")->select();
                    $hasc[$i]["feetype"][$j]["fee"][$k]=0;
                    for($l=0;$l<count($tmp);$l++){
                        if($tmp[$l]["fb_shijitourufeiyong"])
                            $hasc[$i]["feetype"][$j]["fee"][$k]+=$tmp[$l]["fb_shijitourufeiyong"];
                        else
                            $hasc[$i]["feetype"][$j]["fee"][$k]+=$tmp[$l]["pizhunhanshui"];

                    }
                    $hasc[$i]["feetype"][$j]["zong"]+=$hasc[$i]["feetype"][$j]["fee"][$k];
                }
            }
        }

        $this->assign("hasc",$hasc);
        //决策一举三
        $nownian=Db::table("cg_cainian")->where("id=$cid")->find();
        $nowfee=Db::table("cg_ti")->alias("t")
            ->join("cg_ti_list td","t.id=td.ti_id")
            ->join("cg_feiyongkemu3 k3","k3.id=td.feetype")
            ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$nownian["starttime"]} and td.start<={$nownian["endtime"]} and t.jxs_id={$jxs["id"]}")
            ->group("td.feetype")
            ->field("k3.title as feetype,k3.id")->select();
        for($i=0;$i<count($nowfee);$i++){
            $tmp=Db::table("cg_ti")->alias("t")
                    ->join("cg_ti_list td","t.id=td.ti_id")->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$nownian["starttime"]} and td.start<={$nownian["endtime"]} and t.jxs_id={$jxs["id"]} and td.feetype={$nowfee[$i]["id"]}")
                    ->field("td.pizhunhanshui,td.fb_shijitourufeiyong")->select();
            $nowfee[$i]["fee"]=0;
            for($l=0;$l<count($tmp);$l++){
                if($tmp[$l]["fb_shijitourufeiyong"])
                    $nowfee[$i]["fee"]+=$tmp[$l]["fb_shijitourufeiyong"];
                else
                    $nowfee[$i]["fee"]+=$tmp[$l]["pizhunhanshui"];

            }
        }
        $this->assign("nowfee",$nowfee);
        $today=Date("y-m-d");
        $this->assign("today",$today);
        //决策依据四
        $fy=Db::Table("bx_fy")->alias("f")->join("pro_products p","f.product_id=p.id")->where("f.sdAgency_id={$jxs["id"]}")->order("fyDate desc")
        ->field("p.id as pid,p.pro_no,p.pro_name,f.fyDate,f.fyZheMoney")->limit(0,5)->select();
        $this->assign("fy",$fy);
        //决策依据6
        //$zongc=[];
        for($i=0;$i<$zongcai;$i++){
            $cainian[$i]["zongfy"]=0;
            //echo $i;
            $kaohe2=Db::table("cg_cainian")->alias("c")->join("cg_kaoheqi1 k1","k1.cid=c.id")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["c.id"=>$cainian[$i]["id"]])
            ->field("k2.id,k2.title,k2.starttime as k2start,k2.endtime as k2end")->order("k2.starttime")->select();
            //print_r($kaohe2);echo "<br/>";
            $zongc["zongfy"]=0;
            for($j=0;$j<count($kaohe2);$j++){
                $datast=date("Y-m-d",$kaohe2[$j]["k2start"]);
                $dataed=date("Y-m-d",$kaohe2[$j]["k2end"]);
                $sumfy=Db::Table("bx_fy")->where("sdAgency_id={$jxs["id"]} and fyDate>='$datast' and fyDate<='$dataed'")->sum("fyZheMoney");
                if($sumfy)
                    $cainian[$i]["fy"][$j]=$sumfy;
                else
                    $cainian[$i]["fy"][$j]=0;
                $cainian[$i]["zongfy"]+=$cainian[$i]["fy"][$j];
            }
            $cainian[$i]["avgfy"]=$cainian[$i]["zongfy"]/12;
        }
        //print_r($cainian);
       // $this->assign("zongc",$zongc);
        $this->assign("cainian",$cainian);
        return $this->fetch();
    }
    //客户情况总览
    public function jxskh(){
        $sarr=db("cg_market_provice")->where("isdel=0")->field("id,title")->select();
        for($i=0;$i<count($sarr);$i++){
            $zy=db("cg_jxs")->where("provice={$sarr[$i]["id"]} and isdel=0 and direct=1")->count();
            if($zy){
                $sarr[$i]["zy"]=$zy;
            }else{
                $sarr[$i]["zy"]=0;
            }
             $fzy=db("cg_jxs")->where("provice={$sarr[$i]["id"]} and isdel=0 and direct=0")->count();
            if($fzy){
                $sarr[$i]["fzy"]=$zy;
            }else{
                $sarr[$i]["fzy"]=0;
            }
            $sarr[$i]["zong"]=$sarr[$i]["fzy"]+$sarr[$i]["zy"];
        }
        $this->assign("sarr",$sarr);
        return $this->fetch();
    }
    //客户情况详情
    public function jxskhde($tid,$sid){
        $where="j.isdel=0 and j.provice=$sid ";
        if($tid!=2){
            $where.="and j.direct=$tid ";
        }
        $jxs=db("cg_jxs j")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")
        ->join("cg_market_city c","j.city=c.id")->join("employee ez","ez.id=z.head","LEFT")->join("employee ep","ep.id=p.head","LEFT")
        ->join("employee ec","ec.id=c.head","LEFT")
        ->where($where)
        ->field("z.title as fenqu,p.title as sheng,c.title as shi,ez.em_name as fenquhead,ep.em_name as shenghead,ec.em_name as shihead,j.stuff,j.sn,j.title")
        ->select();
        for($i=0;$i<count($jxs);$i++){
            $stuff=unserialize($jxs[$i]['stuff']);
            //print_r($stuff);
            $jxs[$i]["yewu"]=[];
            if($stuff){
                foreach ($stuff as $k => $v) {
                    # code...
                    $jxs[$i]["yewu"][]=db("employee")->where("id={$v}")->value("em_name");
                }
            }
        }
        $this->assign("jxs",$jxs);
        return $this->fetch();
    }
    //客户发运
    public function jxsfy($cid='',$tid=5){
        $this->assign("tid",$tid);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,5)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 2:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,20)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 3:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,50)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 4:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney)")->limit(0,50)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 5:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            
        }
         $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("j.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }
       $this->assign("zong",$zong);
       $this->assign("apply",$apply);
       $this->assign("kaohe2",$kaohe2);
        return $this->fetch();
    }
    public function jxsfydc($cid,$tid){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
       
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,5)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 2:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,20)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 3:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->limit(0,50)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 4:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney)")->limit(0,50)->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 5:
                # code...
                 $apply=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("j.id")->order("sum(fyZheMoney) desc")->field("j.id,j.title as jxs,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            
        }
         $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_jxs j","f.sdAgency_id=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("j.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }
       $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("经销商发运排名");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:R1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "经销商发运排名");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:R2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:R3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"经销商名");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"区域");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"小计");

         $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["zong"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':E'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$kaohe2[0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$kaohe2[1]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kaohe2[2]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kaohe2[3]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kaohe2[4]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kaohe2[5]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kaohe2[6]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kaohe2[7]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kaohe2[8]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kaohe2[9]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$kaohe2[10]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$kaohe2[11]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);

        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //经销商客户信息查询
    public function jxskhinfo($page=1,$list=10){
        $request = Request::instance();
        $data=$request->post();
        $where="1=1 ";
        if($data){
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='{$shi}' ";
            }
            if(trim($data["shihead"])){
                $shihead=trim($data["shihead"]);
                $where.="and ec.em_name='{$shihead}' ";
            }
            if(trim($data["shenghead"])){
                $shenghead=trim($data["shenghead"]);
                $where.="and ep.em_name='{$shenghead}' ";
            }
            if(trim($data["fenquhead"])){
                $fenquhead=trim($data["fenquhead"]);
                $where.="and ez.em_name='{$fenquhead}' ";
            }
            if(trim($data["sn"])){
                $sn=trim($data["sn"]);
                $where.="and j.sn='{$sn}' ";
            }
            if(trim($data["title"])){
                $title=trim($data["title"]);
                $where.="and j.title='{$title}' ";
            }
            if($data["direct"]){
                $direct=$data["direct"]-1;
                $where.="and j.direct=$direct ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","shihead"=>"","shenghead"=>"","fenquhead"=>"","sn"=>"","title"=>"","direct"=>0];
        }
        $sum=db("cg_jxs j")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")
        ->join("cg_market_city c","j.city=c.id")->join("employee ez","ez.id=z.head","LEFT")->join("employee ep","ep.id=p.head","LEFT")
        ->join("employee ec","ec.id=c.head","LEFT")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=db("cg_jxs j")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")
        ->join("cg_market_city c","j.city=c.id")->join("employee ez","ez.id=z.head","LEFT")->join("employee ep","ep.id=p.head","LEFT")
        ->join("employee ec","ec.id=c.head","LEFT")
        ->where($where)
        ->order("j.id")->limit($start,$list)
        ->field("z.title as fenqu,p.title as sheng,c.title as shi,ez.em_name as fenquhead,ep.em_name as shenghead,ec.em_name as shihead,j.stuff,j.sn,j.title,j.bs_contact")
        ->select();
        for($i=0;$i<count($apply);$i++){
            $stuff=unserialize($apply[$i]['stuff']);
            //print_r($stuff);
            $apply[$i]["yewu"]=[];
            if($stuff){
                foreach ($stuff as $k => $v) {
                    # code...
                    $apply[$i]["yewu"][]=db("employee")->where("id={$v}")->value("em_name");
                }
            }
        }
        $this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        return $this->fetch();
    }
    //经销商费用投入
    public function jxstr($cid='',$tid=5){
        $this->assign("tid",$tid);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        //echo $nian;
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); 
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["fy"]=0;
            $kaohe2[$k]["xl"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,5)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 2:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,20)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 3:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,50)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 4:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong)")->limit(0,50)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
             case 5:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,5)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            default:
                # code...
                break;
        }
      
        $zongfy=0;$zongxl=0;
        //print_r($apply);
        for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $fy=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and t.jxs_id={$apply[$i]["id"]}")->sum("td.fb_shijitourufeiyong");
                if($fy){
                    $apply[$i]["fy"][$j]=round($fy,2);
                    $kaohe2[$j]["fy"]+=round($fy,2);
                    $zongfy+=round($fy,2);
                }else{
                    $apply[$i]["fy"][$j]=0;
                }
                $xl=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and t.jxs_id={$apply[$i]["id"]}")->sum("td.fb_shijixiaoliang");
                if($xl){
                    $apply[$i]["xl"][$j]=round($xl,2);
                    $kaohe2[$j]["xl"]+=round($xl,2);
                    $zongxl+=round($xl,2);
                }else{
                    $apply[$i]["xl"][$j]=0;
                }
                if($apply[$i]["xl"][$j]){
                    $apply[$i]["fyl"][$j]=round($apply[$i]["fy"][$j]/$apply[$i]["xl"][$j]*100,2);
                }else{
                    $apply[$i]["fyl"][$j]=0;
                }
            }
            if($apply[$i]["xiao"])
                $apply[$i]["zfyl"]=round($apply[$i]["fei"]/$apply[$i]["xiao"]*100,2);
            else
                $apply[$i]["zfyl"]=0;
        }
        foreach ($kaohe2 as $k => $v) {
            # code...
            if($kaohe2[$k]["xl"]){
                $kaohe2[$k]["fyl"]=round($kaohe2[$k]["fy"]/$kaohe2[$k]["xl"]*100,2);
            }else{
                $kaohe2[$k]["fyl"]=0;
            }
        }
        if($zongxl){
            $zongfyl=round($zongfy/$zongxl*100,2);
        }else{
            $zongfyl=0;
        }
        $this->assign("zongfy",$zongfy);
        $this->assign("zongxl",$zongxl);
        $this->assign("zongfyl",$zongfyl);
        $this->assign("kaohe2",$kaohe2);
        $this->assign("apply",$apply);
        return $this->fetch();
    }
    public function jxstrdc($cid,$tid){
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
       
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); 
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["fy"]=0;
            $kaohe2[$k]["xl"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,5)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 2:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,20)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 3:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,50)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            case 4:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong)")->limit(0,50)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
             case 5:
                # code...
                  $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                    ->group("t.jxs_id")->order("sum(td.fb_shijitourufeiyong) desc")->limit(0,5)
                    ->field("sum(td.fb_shijitourufeiyong) as fei,sum(td.fb_shijixiaoliang) as xiao,j.id,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jxs")
                    ->select();
                break;
            default:
                # code...
                break;
        }
      
        $zongfy=0;$zongxl=0;
        //print_r($apply);
        for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $fy=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and t.jxs_id={$apply[$i]["id"]}")->sum("td.fb_shijitourufeiyong");
                if($fy){
                    $apply[$i]["fy"][$j]=round($fy,2);
                    $kaohe2[$j]["fy"]+=round($fy,2);
                    $zongfy+=round($fy,2);
                }else{
                    $apply[$i]["fy"][$j]=0;
                }
                $xl=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and t.jxs_id={$apply[$i]["id"]}")->sum("td.fb_shijixiaoliang");
                if($xl){
                    $apply[$i]["xl"][$j]=round($xl,2);
                    $kaohe2[$j]["xl"]+=round($xl,2);
                    $zongxl+=round($xl,2);
                }else{
                    $apply[$i]["xl"][$j]=0;
                }
                if($apply[$i]["xl"][$j]){
                    $apply[$i]["fyl"][$j]=round($apply[$i]["fy"][$j]/$apply[$i]["xl"][$j]*100,2);
                }else{
                    $apply[$i]["fyl"][$j]=0;
                }
            }
            if($apply[$i]["xiao"])
                $apply[$i]["zfyl"]=round($apply[$i]["fei"]/$apply[$i]["xiao"]*100,2);
            else
                $apply[$i]["zfyl"]=0;
        }
        foreach ($kaohe2 as $k => $v) {
            # code...
            if($kaohe2[$k]["xl"]){
                $kaohe2[$k]["fyl"]=round($kaohe2[$k]["fy"]/$kaohe2[$k]["xl"]*100,2);
            }else{
                $kaohe2[$k]["fyl"]=0;
            }
        }
        if($zongxl){
            $zongfyl=round($zongfy/$zongxl*100,2);
        }else{
            $zongfyl=0;
        }

         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}年经销商费用投入情况");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:S1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}年经销商费用投入情况");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:S2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"分区");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"经销商");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"项目");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('S3',"小计");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;$ed=$h+2;
            $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:A{$ed}");
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->mergeCells("B{$h}:B{$ed}");
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('B'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->mergeCells("C{$h}:C{$ed}");
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('C'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);

            $objPHPExcel->getActiveSheet()->mergeCells("D{$h}:D{$ed}");
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('D'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);

            $objPHPExcel->getActiveSheet()->mergeCells("E{$h}:E{$ed}");
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('E'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"费用",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["fy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["fy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["fy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["fy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["fy"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["fy"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["fy"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["fy"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["fy"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["fy"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["fy"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["fy"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["fei"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"销量",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["xl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["xl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["xl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["xl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xl"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["xl"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["xl"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["xl"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["xl"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["xl"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["xl"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["xl"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["xiao"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["xl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["xl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["xl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["xl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][4])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["xl"][4].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][5])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["xl"][5].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][6])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["xl"][6].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][7])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["xl"][7].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][8])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["xl"][8].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][9])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["xl"][9].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][10])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["xl"][10].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xl"][11])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["xl"][11].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["xiao"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["xiao"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:S'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //经销商目标达成
    public function jxsmb($cid='',$zid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        $this->assign("kaohe1",$kaohe1);
        $where="isdel=0 ";
        if(!$zid){
            $zid=db("cg_jxs")->where($where)->value("id");
        }
        $where.="and id in ($zid) ";
        $zarr=[];
        if($zid){
            $zarr=db("cg_jxs")->where($where)->field("id,title")->select();
        }

        for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.id={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.id={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
        $this->assign("zarr",$zarr);

        return $this->fetch();
    }
    public function jxsmbout($cid,$zid){
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $kaohe1=db("cg_kaoheqi1")->where(["cid"=>$cid])->select();
        $where="isdel=0 ";
        if(!$zid){
            $zid=db("cg_jxs")->where($where)->value("id");
        }else{
            $zid=implode(',',explode('-',$zid));
        }
        $where.="and id in ($zid) ";
        $zarr=[];
        if($zid){
            $zarr=db("cg_jxs")->where($where)->field("id,title")->select();
        }

        for($i=0;$i<count($zarr);$i++){
            $zarr[$i]["zyjxl"]=0;$zarr[$i]["zyjfy"]=0;$zarr[$i]["zxldc"]=0;$zarr[$i]["zfysq"]=0;$zarr[$i]["zyjfyl"]=0;
            $zarr[$i]["zsjfyl"]=0;$zarr[$i]["zdcl"]='-';
            for($j=0;$j<count($kaohe1);$j++){
                $ed=$kaohe1[$j]["endtime"]+3600*24;
                //预计销量
                $yjxl=0;
                $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$zarr[$i]["id"]} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpxl){
                    $yjxl=round($tmpxl,2);
                }
                $zarr[$i]["yjxl"][$j]=$yjxl;
                $zarr[$i]["zyjxl"]+=$yjxl;
                //预计费用预算
                $yjfy=0;
                $tmpfy=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                ->where("td.jingxiao={$zarr[$i]["id"]} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe1[$j]["id"]}")->sum("feiyong");
                if($tmpfy){
                    $yjfy=round($tmpfy,2);
                }
                $zarr[$i]["yjfy"][$j]=$yjfy;
                $zarr[$i]["zyjfy"]+=$yjfy;
                //销量达成
                $xldc=0;
                $tmpxdc=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.id={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("totalExpectedSales,fb_shijixiaoliang")->select();
                foreach ($tmpxdc as $k => $v) {
                    # code...
                    if($v["fb_shijixiaoliang"]){
                        $xldc+=$v["fb_shijixiaoliang"];
                    }else{
                        $xldc+=$v["totalExpectedSales"];
                    }
                }
                $zarr[$i]["xldc"][$j]=round($xldc,2);
                $zarr[$i]["zxldc"]+=round($xldc,2);
                //费用申请
                $fysq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")->where("j.id={$zarr[$i]["id"]} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe1[$j]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                foreach ($tmpsq as $k => $v) {
                    # code...
                    if($v["fb_shijitourufeiyong"]){
                        $fysq+=$v["fb_shijitourufeiyong"];
                    }else{
                        $fysq+=$v["yjsjtrfy"];
                    }
                }
                $zarr[$i]["fysq"][$j]=round($fysq,2);
                $zarr[$i]["zfysq"]+=round($fysq,2);
                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["yjfyl"][$j]=round($zarr[$i]["yjfy"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["yjfyl"][$j]=0;
                }

                if($zarr[$i]["xldc"][$j]){
                    $zarr[$i]["sjfyl"][$j]=round($zarr[$i]["fysq"][$j]/$zarr[$i]["xldc"][$j]*100,2);
                }else{
                    $zarr[$i]["sjfyl"][$j]=0;
                }

                if($zarr[$i]["yjxl"][$j]){
                    $zarr[$i]["dcl"][$j]=round($zarr[$i]["xldc"][$j]/$zarr[$i]["yjxl"][$j]*100,2);
                }else{
                    $zarr[$i]["dcl"][$j]='-';
                }
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zyjfyl"]=round($zarr[$i]["zyjfy"]/$zarr[$i]["zyjxl"]*100,2);
            }
            if($zarr[$i]["zxldc"]){
                $zarr[$i]["zsjfyl"]=round($zarr[$i]["zfysq"]/$zarr[$i]["zxldc"]*100,2);
            }
            if($zarr[$i]["zyjxl"]){
                $zarr[$i]["zdcl"]=round($zarr[$i]["zxldc"]/$zarr[$i]["zyjxl"]*100,2);
            }
        }
         //excel
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();
        $title=$nian."财年经销商目标达成报表";
        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
                
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($title);
       
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);

       

        
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "$title");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:G2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
        #####地域信息
        $objPHPExcel->getActiveSheet()->mergeCells('A3:G3');
                 //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
         //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(10);

        $str="包含经销商:";
        if($zarr){
            $tmparr=[];
            foreach ($zarr as $k => $v) {
                # code...
                $tmparr[]=$v["title"];
            }
            $str.=implode(',',$tmparr);
        }else{
            $str.="无";
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A3',$str);

         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A4:G4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A4',"经销商名称");
        $objPHPExcel->getActiveSheet()->setCellValue('C4',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D4',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E4',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F4',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G4',"合计");
        $jarr=$zarr;
        $h=4;
        for($i=0;$i<count($jarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.($h+6));
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$jarr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计销售目标(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjxl"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjxl"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjxl"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjxl"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjxl"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用预算(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfy"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfy"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfy"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfy"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfy"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际销售达成(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["xldc"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["xldc"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["xldc"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["xldc"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zxldc"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用申请(元)",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["fysq"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["fysq"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["fysq"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["fysq"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zfysq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"预计费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["yjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["yjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["yjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["yjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zyjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际费用率",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["sjfyl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["sjfyl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["sjfyl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["sjfyl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zsjfyl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,"实际达成率",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][0])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$jarr[$i]["dcl"][0].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
           if($jarr[$i]["dcl"][1])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$jarr[$i]["dcl"][1].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][2])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$jarr[$i]["dcl"][2].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["dcl"][3])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$jarr[$i]["dcl"][3].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            if($jarr[$i]["zdcl"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$jarr[$i]["zdcl"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,"-",PHPExcel_Cell_DataType::TYPE_STRING);
            
          
        }
           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:G'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
        //经销商科目
    public function jxskemu($cid="",$zid=""){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
      
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
      
         
        if($zid){
            $fenqu=db("cg_jxs")->where("id in ($zid)")->select();
            $tmp=db("cg_jxs")->where("id in ($zid)")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }else{
           $fenqu=db("cg_jxs")->select();
            $tmp=db("cg_jxs")->field("id")->select();
            $jxs=[];
            foreach ($tmp as $key => $v) {
                # code...
                $jxs[]=$v["id"];
            }
        }
        $this->assign("fenqu",$fenqu);
        if($jxs){
            $jxs=implode(',',$jxs);
            $kemu=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
            ->join("cg_feiyongkemu2 k2","k3.pid=k2.id")->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->group("td.feetype")
            ->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]}")
            ->field("td.feetype as id,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1")->select();
        }else{
            $kemu=[];
        }
        $zong=0;
        for($i=0;$i<count($kemu);$i++){
            $tmps=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id in ($jxs) and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$cn["starttime"]} and td.start<{$cn["endtime"]} and td.feetype={$kemu[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui,hx_byhx")->select();
            $jine=0;
            foreach ($tmps as $k => $v) {
                # code...
                if($v["fb_shijiweishui"]){
                    $jine+=$v["fb_shijiweishui"];
                }else{
                    $jine+=$v["pizhunweishui"];
                }
                if($v["hx_byhx"]){
                    $jine-=$v["hx_byhx"];
                }
            }

            $kemu[$i]["jine"]=round($jine,2);
            $zong+=round($jine,2);
        }
        foreach ($kemu as $k => $v) {
            # code...
            $kemu[$k]["bili"]=round($v["jine"]/$zong*100,2);
        }
        $this->assign("kemu",$kemu);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    //经销商营销
    public function jxsyx($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $fenqu=db("cg_market_zoning")->where("isdel=0")->select();
        $this->assign("fenqu",$fenqu);

        $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
        $this->assign("kemu",$kemu);
        return $this->fetch();
    }
    public function query(){
        $request = Request::instance();
        $data = $request->param();
        //print_r($data);
        $cainian=db("cg_cainian")->where(["id"=>$data["cid"]])->find();
        $this->assign("cid",$data["cid"]);
        $this->assign("cainian",$cainian);
        $kaohe1=db("cg_kaoheqi1")->where("cid={$data["cid"]}")->order("starttime")->select();
        $this->assign("kaohe1",$kaohe1);
        $pid=$data["city"];
        $farr=db("cg_jxs")->where("isdel=0 and city=$pid")->field("id,title")->select();
        if($data["fx_kind"]==2){
            $kid=implode(',',$data["kemu"]);
            $kidstr=implode('-',$data["kemu"]);
            $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        }else{
            $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
            $karr=[];
            foreach ($kemu as $key => $value) {
                # code...
                $karr[]=$value["id"];
            }
            $kidstr=implode('-',$karr);
        }
        $this->assign("kid",$kidstr);
        $this->assign("pid",$pid);
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe1);$k++){
                    $start=$kaohe1[$k]["starttime"];
                    $end=$kaohe1[$k]["endtime"]+3600*24;
                    $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                    ->where($where."and j.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
        $this->assign("farr",$farr);
        return $this->fetch();
    }
     public function daochuqu($pid,$cid,$kid){
        $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
        $kaohe1=db("cg_kaoheqi1")->where("cid=$cid")->order("starttime")->select();
        $farr=db("cg_jxs")->where("isdel=0 and city=$pid")->field("id,title")->select();
        $kid=implode(',',explode('-',$kid));
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe1);$k++){
                    $start=$kaohe1[$k]["starttime"];
                    $end=$kaohe1[$k]["endtime"]+3600*24;
                     $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                    ->where($where."and j.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("经销商营销费用报表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:H1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "经销商营销费用报表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:H2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"经销商名");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"第一季度");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"第二季度");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"第三季度");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"第四季度");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"平均");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"小计");

        $h=3;
        for($i=0;$i<count($farr);$i++){
            $h++;$endh=$h+$farr[$i]["ckemu"]-1;
            if($farr[$i]["ckemu"]>1){
                $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.$endh);
            }
            
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$farr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][0]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][0]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][0]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][0]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][0]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][0]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            for($j=1;$j<count($kemu);$j++){
                $h++;
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][$j]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][$j]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][$j]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][$j]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][$j]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][$j]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][$j]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:H'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    public function propotionpie($fid,$kid,$cid){
        $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
        $start=$cainian["starttime"];
        $end=$cainian["endtime"]+3600*24;
        $this->assign("cainian",$cainian);
        $fenqu=db("cg_jxs")->where("id=$fid")->value("title");
        $this->assign("fenqu",$fenqu);
        $karr=explode('-',$kid);
        $kid=implode(',',$karr);
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->field("id,title")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($kemu);$i++){
            $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                    ->where($where."and j.id={$fid} and td.feetype={$kemu[$i]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
            $kemu[$i]["jine"]=0;
            foreach ($tmpjine as $k => $v) {
                # code...
                if($v["fb_shijitourufeiyong"])
                    $kemu[$i]["jine"]+=$v["fb_shijitourufeiyong"];
                else
                    $kemu[$i]["jine"]+=$v["yjsjtrfy"];
            }
        }
        //print_r($kemu);
        $this->assign("kemu",$kemu);
        return $this->fetch();
    }
    //经销商盈利分析
    //kid 1一级考核期  2二级考核期
    public function jxsyl($cid='',$kid=1,$fenqu=0,$provice=0,$city=0,$jxs=0){
        $this->assign("kid",$kid);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $zoning=db("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
        $this->assign("zoning",$zoning);
        if($kid==1){
            $kaohe=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->field("id,title,starttime,endtime")->select();
        }else{
            $kaohe=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.starttime as k1start,k1.endtime as k1end,k1.id as k1id")->select();
        }
       
        $sheng=[];
        if($fenqu){
            $sheng=db("cg_market_provice")->where(["zoning"=>$fenqu,"isdel"=>0])->field("id,title")->select();
        }
        $this->assign("sheng",$sheng);
        $shi=[];
        if($provice){
            $shi=db("cg_market_city")->where(["provice"=>$provice,"isdel"=>0])->field("id,title")->select();
        }
        $this->assign("shi",$shi);
        $jingxiao=[];
        if($city){
            $jingxiao=db("cg_jxs")->where(["city"=>$city,"isdel"=>0])->field("id,title")->select();
        }
        $this->assign("jingxiao",$jingxiao);
        $this->assign("fenqu",$fenqu);$this->assign("provice",$provice);$this->assign("city",$city);$this->assign("jxs",$jxs);
        if($jxs){
            $agen=db("cg_jxs")->where("id=$jxs")->field("id,title,sn")->find();
            $agen["tar"]=0;$agen["fy"]=0;$agen["ys"]=0;$agen["sq"]=0;
            for($i=0;$i<count($kaohe);$i++){
                $kaohe[$i]["startdate"]=date("Y-m-d",$kaohe[$i]["starttime"]);
                $kaohe[$i]["enddate"]=date("Y-m-d",$kaohe[$i]["endtime"]);
                $ed=$kaohe[$i]["endtime"]+3600*24;
                if($kid==1){
                    //一级考核期
                    //销售目标
                    $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpxl)
                        $kaohe[$i]["tar"]=round($tmpxl,2);
                    else
                        $kaohe[$i]["tar"]=0;
                    //费用预算
                    $tmpys=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpys)
                        $kaohe[$i]["ys"]=round($tmpys,2);
                    else
                        $kaohe[$i]["ys"]=0;
                }else{
                    //二级考核期标准
                    $ts=($kaohe[$i]["endtime"]-$kaohe[$i]["starttime"]/3600/24)+1;
                    $yijits=($kaohe[$i]["k1end"]-$kaohe[$i]["k1start"]/3600/24)+1;

                    $kaohe[$i]["tar"]=0;
                    //销售目标
                    $tmpxl1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                    if($tmpxl1){
                        $kaohe[$i]["tar"]+=round($tmpxl1/$yijits*$ts,2);
                    }

                    $tmpxl2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpxl2)
                        $kaohe[$i]["tar"]+=round($tmpxl2,2);
                    //费用预算
                    $kaohe[$i]["ys"]=0;
                    $tmpys1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                    if($tmpys1){
                        $kaohe[$i]["ys"]+=round($tmpys1/$yijits*$ts,2);
                    }

                    $tmpys2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpys2)
                        $kaohe[$i]["ys"]+=round($tmpys2,2);
                }
                //实际发运
                $tmpfy=db("bx_fy")->where("sdAgency_id=$jxs and fyDate>='{$kaohe[$i]["startdate"]}' and fyDate<='{$kaohe[$i]["enddate"]}'")->sum("fyZheMoney");
                if($tmpfy){
                    $kaohe[$i]["fy"]=round($tmpfy,2);
                }else{
                    $kaohe[$i]["fy"]=0;
                }
                //实际费用
                $sq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxs} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe[$i]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                //echo db("cg_ti")->getLastsql();echo "<br>";
                foreach($tmpsq as $k=>$v){
                    if($v["fb_shijitourufeiyong"])
                        $sq+=$v["fb_shijitourufeiyong"];
                    else{
                        $sq+=$v["yjsjtrfy"];
                    }
                }
                $kaohe[$i]["sq"]=round($sq,2);
                if($kaohe[$i]["tar"]){
                    $kaohe[$i]["dc"]=round($kaohe[$i]["fy"]/$kaohe[$i]["tar"]*100,2);
                }else{
                    $kaohe[$i]["dc"]=0;
                }
                if($kaohe[$i]["ys"]){
                    $kaohe[$i]["ff"]=round($kaohe[$i]["sq"]/$kaohe[$i]["ys"]*100,2);
                }else{
                    $kaohe[$i]["ff"]=0;
                }
                $agen["tar"]+=$kaohe[$i]["tar"];$agen["fy"]+=$kaohe[$i]["fy"];$agen["ys"]+=$kaohe[$i]["ys"];$agen["sq"]+=$kaohe[$i]["sq"];

            }
            $agen["dc"]=0;
            if($agen["tar"]){
                $agen["dc"]=round($agen["fy"]/$agen["tar"]*100,2);
            }
            $agen["ff"]=0;
            if($agen["ys"]){
                $agen["ff"]=round($agen["sq"]/$agen["ys"]*100,2);
            }
            $this->assign("agen",$agen); 
        }
        $this->assign("kaohe",$kaohe);
        return $this->fetch();
    }
    public function jxsyldc($cid,$kid,$jxs){
        if($kid==1){
            $kaohe=db("cg_kaoheqi1")->where(["cid"=>$cid])->order("starttime")->field("id,title,starttime,endtime")->select();
        }else{
            $kaohe=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["k1.cid"=>$cid])->order("k2.starttime")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.starttime as k1start,k1.endtime as k1end,k1.id as k1id")->select();
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $agen=db("cg_jxs")->where("id=$jxs")->field("id,title,sn")->find();
            $agen["tar"]=0;$agen["fy"]=0;$agen["ys"]=0;$agen["sq"]=0;
            for($i=0;$i<count($kaohe);$i++){
                $kaohe[$i]["startdate"]=date("Y-m-d",$kaohe[$i]["starttime"]);
                $kaohe[$i]["enddate"]=date("Y-m-d",$kaohe[$i]["endtime"]);
                $ed=$kaohe[$i]["endtime"]+3600*24;
                if($kid==1){
                    //一级考核期
                    //销售目标
                    $tmpxl=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpxl)
                        $kaohe[$i]["tar"]=round($tmpxl,2);
                    else
                        $kaohe[$i]["tar"]=0;
                    //费用预算
                    $tmpys=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpys)
                        $kaohe[$i]["ys"]=round($tmpys,2);
                    else
                        $kaohe[$i]["ys"]=0;
                }else{
                    //二级考核期标准
                    $ts=($kaohe[$i]["endtime"]-$kaohe[$i]["starttime"]/3600/24)+1;
                    $yijits=($kaohe[$i]["k1end"]-$kaohe[$i]["k1start"]/3600/24)+1;

                    $kaohe[$i]["tar"]=0;
                    //销售目标
                    $tmpxl1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                    if($tmpxl1){
                        $kaohe[$i]["tar"]+=round($tmpxl1/$yijits*$ts,2);
                    }

                    $tmpxl2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=5 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpxl2)
                        $kaohe[$i]["tar"]+=round($tmpxl2,2);
                    //费用预算
                    $kaohe[$i]["ys"]=0;
                    $tmpys1=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.yijikaoheqi={$kaohe[$i]["k1id"]} and td.erjikaoheqi is null")->sum("feiyong");
                    if($tmpys1){
                        $kaohe[$i]["ys"]+=round($tmpys1/$yijits*$ts,2);
                    }

                    $tmpys2=db("target_apply t")->join("target_apply_detail td","td.apply_id=t.id")
                    ->where("td.jingxiao={$jxs} and t.type_id=2 and t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.tag<>1 and td.erjikaoheqi={$kaohe[$i]["id"]}")->sum("feiyong");
                    if($tmpys2)
                        $kaohe[$i]["ys"]+=round($tmpys2,2);
                }
                //实际发运
                $tmpfy=db("bx_fy")->where("sdAgency_id=$jxs and fyDate>='{$kaohe[$i]["startdate"]}' and fyDate<='{$kaohe[$i]["enddate"]}'")->sum("fyZheMoney");
                if($tmpfy){
                    $kaohe[$i]["fy"]=round($tmpfy,2);
                }else{
                    $kaohe[$i]["fy"]=0;
                }
                //实际费用
                $sq=0;
                $tmpsq=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.jxs_id={$jxs} and t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe[$i]["starttime"]} and td.start<{$ed}")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                //echo db("cg_ti")->getLastsql();echo "<br>";
                foreach($tmpsq as $k=>$v){
                    if($v["fb_shijitourufeiyong"])
                        $sq+=$v["fb_shijitourufeiyong"];
                    else{
                        $sq+=$v["yjsjtrfy"];
                    }
                }
                $kaohe[$i]["sq"]=round($sq,2);
                if($kaohe[$i]["tar"]){
                    $kaohe[$i]["dc"]=round($kaohe[$i]["fy"]/$kaohe[$i]["tar"]*100,2);
                }else{
                    $kaohe[$i]["dc"]=0;
                }
                if($kaohe[$i]["ys"]){
                    $kaohe[$i]["ff"]=round($kaohe[$i]["sq"]/$kaohe[$i]["ys"]*100,2);
                }else{
                    $kaohe[$i]["ff"]=0;
                }
                $agen["tar"]+=$kaohe[$i]["tar"];$agen["fy"]+=$kaohe[$i]["fy"];$agen["ys"]+=$kaohe[$i]["ys"];$agen["sq"]+=$kaohe[$i]["sq"];

            }
            $agen["dc"]=0;
            if($agen["tar"]){
                $agen["dc"]=round($agen["fy"]/$agen["tar"]*100,2);
            }
            $agen["ff"]=0;
            if($agen["ys"]){
                $agen["ff"]=round($agen["sq"]/$agen["ys"]*100,2);
            }
        if($kid==1){
            $endh='H';
        }else{
            $endh='P';
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}经销商盈利分析");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells("A1:{$endh}1");
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}经销商盈利分析");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells("A2:{$endh}2");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle("A3:{$endh}3")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle("A3:{$endh}3")->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle("A3:{$endh}3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle("A3:{$endh}3")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"经销商编号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"经销商名");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"项目");
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValue(chr(100+$i).'3',$kaohe[$i]["title"]);
        }
        $objPHPExcel->getActiveSheet()->setCellValue("{$endh}3","合计");
        $h=4;$eh=$h+7;
        $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:A{$eh}");
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$agen["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->mergeCells("B{$h}:B{$eh}");
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('B'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$agen["title"],PHPExcel_Cell_DataType::TYPE_STRING);

        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"销售目标",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["tar"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["tar"],PHPExcel_Cell_DataType::TYPE_STRING);

        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"实际发运",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["fy"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["fy"],PHPExcel_Cell_DataType::TYPE_STRING);

        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"销售达成率",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["dc"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["dc"].'%',PHPExcel_Cell_DataType::TYPE_STRING);

         $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"成本",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,'-',PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",'-',PHPExcel_Cell_DataType::TYPE_STRING);
         $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"费用预算",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["ys"],PHPExcel_Cell_DataType::TYPE_STRING);
         $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"实际费用",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["sq"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["sq"],PHPExcel_Cell_DataType::TYPE_STRING);
          $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"费用投放率",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,$kaohe[$i]["ff"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",$agen["ff"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
           $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,"利润",PHPExcel_Cell_DataType::TYPE_STRING);
        for($i=0;$i<count($kaohe);$i++){
            $objPHPExcel->getActiveSheet()->setCellValueExplicit(chr(100+$i).$h,'-',PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValueExplicit("{$endh}{$h}",'-',PHPExcel_Cell_DataType::TYPE_STRING);


         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:'.$endh.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            for($i=0;$i<count($kaohe);$i++){
                $objPHPExcel->getActiveSheet()->getColumnDimension(chr(100+$i))->setWidth(20);
            }
            $objPHPExcel->getActiveSheet()->getColumnDimension($endh)->setWidth(20);

         $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  

    }
    //经销商目标报表
    public function jxsmbb(){
        return $this->fetch();
    }
}